﻿Imports System.Data.SqlClient
Imports iInterface.Data
Imports System.Data
Imports System.IO

Public Class mDataStore
    '****************************************SAMPLE CODE HERE***********************************************
    'Public Function fLogMeIn(ByVal sUserName As String, ByVal sPassword As String) As DataSet
    '    Dim SQLCommand As New SqlCommand
    '    Dim resultDataSet As New DataSet

    '    With SQLCommand
    '        .CommandType = CommandType.StoredProcedure
    '        .CommandText = "foUserLogin"
    '    End With
    '    AddParameter(SQLCommand, "@UserName", SqlDbType.NVarChar, ParameterDirection.Input, sUserName)
    '    AddParameter(SQLCommand, "@Password", SqlDbType.NVarChar, ParameterDirection.Input, sPassword)
    '    resultDataSet = ExecuteSQLCommandDS(SQLCommand)
    '    SQLCommand.Dispose()
    '    Return resultDataSet
    'End Function

    'Public Sub mProgressMe(ByVal sID As String, ByVal sStatus As String)
    '    Dim SQLCommand As New SqlCommand
    '    With SQLCommand
    '        .CommandType = CommandType.StoredProcedure
    '        .CommandText = "foProgressMe"
    '    End With
    '    AddParameter(SQLCommand, "@ID", SqlDbType.NVarChar, ParameterDirection.Input, sID)
    '    AddParameter(SQLCommand, "@Status", SqlDbType.NVarChar, ParameterDirection.Input, sStatus)
    '    ExecuteSQLCommand(SQLCommand)
    '    SQLCommand.Dispose()
    'End Sub

    'Public Function fGetAllLocation() As DataSet
    '    Dim SQLCommand As New SqlCommand
    '    Dim resultDataSet As New DataSet
    '    With SQLCommand
    '        .CommandType = CommandType.StoredProcedure
    '        .CommandText = "foGetAllLocation"
    '    End With

    '    resultDataSet = ExecuteSQLCommandDS(SQLCommand)

    '    SQLCommand.Dispose()

    '    Return resultDataSet
    'End Function
    '******************************************END SAMPLE CODE **********************************************************

#Region "Import Data"

    Public Shared Sub mAddLecturer(ByVal sNumber As String, ByVal sName As String, ByVal iNoSession As Integer, ByVal s1Choice As String, ByVal s2Choice As String, ByVal s3Choice As String, ByVal sNote As String)
        Dim SQLCommand As New SqlCommand
        With SQLCommand
            .CommandType = CommandType.StoredProcedure
            .CommandText = "foAddLecturer"
        End With
        AddParameter(SQLCommand, "@Number", SqlDbType.NVarChar, ParameterDirection.Input, sNumber)
        AddParameter(SQLCommand, "@Name", SqlDbType.NVarChar, ParameterDirection.Input, sName)
        AddParameter(SQLCommand, "@NoSession", SqlDbType.Int, ParameterDirection.Input, iNoSession)
        AddParameter(SQLCommand, "@1Choice", SqlDbType.NVarChar, ParameterDirection.Input, s1Choice)
        AddParameter(SQLCommand, "@2Choice", SqlDbType.NVarChar, ParameterDirection.Input, s2Choice)
        AddParameter(SQLCommand, "@3Choice", SqlDbType.NVarChar, ParameterDirection.Input, s3Choice)
        AddParameter(SQLCommand, "@Note", SqlDbType.NVarChar, ParameterDirection.Input, sNote)
        ExecuteSQLCommand(SQLCommand)
        SQLCommand.Dispose()
    End Sub

    Public Shared Sub mAddExp(ByVal sValue As String, ByVal sTarget As String)
        Dim SQLCommand As New SqlCommand
        With SQLCommand
            .CommandType = CommandType.StoredProcedure
            .CommandText = "foAddExp"
        End With
        AddParameter(SQLCommand, "@Value", SqlDbType.NVarChar, ParameterDirection.Input, sValue)
        AddParameter(SQLCommand, "@LecturerNumber", SqlDbType.NVarChar, ParameterDirection.Input, sTarget)
        'check exist, update/insert
        ExecuteSQLCommand(SQLCommand)
        SQLCommand.Dispose()
    End Sub
    Public Shared Sub mAddGroup(ByVal sGroup As String, ByVal dtTo As Date, ByVal dtFrom As Date, ByVal dtDate As Date)
        Dim dtTempFrom As Date = CDate(dtDate.ToString("DD MMM yyyy") & " " & dtFrom.ToString("HH:mm"))
        Dim dtTempTo As Date = CDate(dtDate.ToString("DD MMM yyyy") & " " & dtTo.ToString("HH:mm"))
        Dim SQLCommand As New SqlCommand
        With SQLCommand
            .CommandType = CommandType.StoredProcedure
            .CommandText = "foAddGroup"
        End With
        AddParameter(SQLCommand, "@Group", SqlDbType.NVarChar, ParameterDirection.Input, sGroup)
        AddParameter(SQLCommand, "@To", SqlDbType.DateTime, ParameterDirection.Input, dtTempTo)
        AddParameter(SQLCommand, "@From", SqlDbType.DateTime, ParameterDirection.Input, dtTempFrom)
        'check exist, update/insert
        ExecuteSQLCommand(SQLCommand)
        SQLCommand.Dispose()
    End Sub
    Public Shared Sub mAddGroupStudent(ByVal sGroup As String)

        Dim SQLCommand As New SqlCommand
        With SQLCommand
            .CommandType = CommandType.StoredProcedure
            .CommandText = "foAddGroupStudent"
        End With
        AddParameter(SQLCommand, "@Group", SqlDbType.NVarChar, ParameterDirection.Input, sGroup)
        'check exist, update/insert
        ExecuteSQLCommand(SQLCommand)
        SQLCommand.Dispose()
    End Sub
    Public Shared Sub mAddStudent(ByVal StdNumber As String, ByVal StdName As String, ByVal sPosition As String, ByVal sCompany As String, ByVal sGroup As String, ByVal AreaOfExp As String)
        Dim SQLCommand As New SqlCommand
        With SQLCommand
            .CommandType = CommandType.StoredProcedure
            .CommandText = "foAddStudent"
        End With
        AddParameter(SQLCommand, "@StdNumber", SqlDbType.NVarChar, ParameterDirection.Input, StdNumber)
        AddParameter(SQLCommand, "@StdName", SqlDbType.NVarChar, ParameterDirection.Input, StdName)
        AddParameter(SQLCommand, "@sPosition", SqlDbType.NVarChar, ParameterDirection.Input, sPosition)
        AddParameter(SQLCommand, "@sCompany", SqlDbType.NVarChar, ParameterDirection.Input, sCompany)
        AddParameter(SQLCommand, "@sGroup", SqlDbType.NVarChar, ParameterDirection.Input, sGroup)
        AddParameter(SQLCommand, "@AreaOfExp", SqlDbType.NVarChar, ParameterDirection.Input, AreaOfExp)

        ExecuteSQLCommand(SQLCommand)
        SQLCommand.Dispose()
    End Sub
#End Region

#Region "Lecturer Student DraftSchedule"

    Public Shared Function GetLecturerList() As DataSet
        Dim SQLCommand As New SqlCommand
        Dim resultDataSet As New DataSet
        With SQLCommand
            .CommandType = CommandType.StoredProcedure
            .CommandText = "foGetLecturerList"
        End With

        resultDataSet = ExecuteSQLCommandDS(SQLCommand)

        SQLCommand.Dispose()

        Return resultDataSet
    End Function
    Public Shared Function GetStudentList() As DataSet
        Dim SQLCommand As New SqlCommand
        Dim resultDataSet As New DataSet
        With SQLCommand
            .CommandType = CommandType.StoredProcedure
            .CommandText = "foGetStudentList"
        End With

        resultDataSet = ExecuteSQLCommandDS(SQLCommand)

        SQLCommand.Dispose()

        Return resultDataSet
    End Function
    Public Shared Function GetLecturerExp(ByVal sLecturerNumber As String) As DataSet
        Dim SQLCommand As New SqlCommand
        Dim resultDataSet As New DataSet
        With SQLCommand
            .CommandType = CommandType.StoredProcedure
            .CommandText = "foGetLecturerExp"
        End With
        AddParameter(SQLCommand, "@LecturerNumber", SqlDbType.NVarChar, ParameterDirection.Input, sLecturerNumber)
        resultDataSet = ExecuteSQLCommandDS(SQLCommand)

        SQLCommand.Dispose()

        Return resultDataSet
    End Function

    Public Shared Function GetGroups() As DataSet
        Dim SQLCommand As New SqlCommand
        Dim resultDataSet As New DataSet
        With SQLCommand
            .CommandType = CommandType.StoredProcedure
            .CommandText = "foGetGroup"
        End With
        resultDataSet = ExecuteSQLCommandDS(SQLCommand)

        SQLCommand.Dispose()

        Return resultDataSet
    End Function
    Public Shared Function GetAllSession() As DataSet
        Dim SQLCommand As New SqlCommand
        Dim resultDataSet As New DataSet
        With SQLCommand
            .CommandType = CommandType.StoredProcedure
            .CommandText = "foGetAllSession"
        End With
        resultDataSet = ExecuteSQLCommandDS(SQLCommand)

        SQLCommand.Dispose()

        Return resultDataSet
    End Function
    Public Shared Function GetLecturerSession(ByVal sGroup As String, ByVal sSession As String) As DataSet
        Dim SQLCommand As New SqlCommand
        Dim resultDataSet As New DataSet
        With SQLCommand
            .CommandType = CommandType.StoredProcedure
            .CommandText = "foGetLecturerSession"
        End With
        AddParameter(SQLCommand, "@Group", SqlDbType.NVarChar, ParameterDirection.Input, sGroup)
        AddParameter(SQLCommand, "@Session", SqlDbType.NVarChar, ParameterDirection.Input, sSession)
        resultDataSet = ExecuteSQLCommandDS(SQLCommand)

        SQLCommand.Dispose()

        Return resultDataSet
    End Function
    Public Shared Function GetStudentSession(ByVal sGroup As String, ByVal sSession As String) As DataSet
        Dim SQLCommand As New SqlCommand
        Dim resultDataSet As New DataSet
        With SQLCommand
            .CommandType = CommandType.StoredProcedure
            .CommandText = "foGetStudentSession"
        End With
        AddParameter(SQLCommand, "@GroupSession", SqlDbType.NVarChar, ParameterDirection.Input, sGroup & "_" & sSession)
        resultDataSet = ExecuteSQLCommandDS(SQLCommand)

        SQLCommand.Dispose()

        Return resultDataSet
    End Function
    Public Shared Function GetAvailableLecturerDatabase() As DataSet
        Dim SQLCommand As New SqlCommand
        Dim resultDataSet As New DataSet
        With SQLCommand
            .CommandType = CommandType.StoredProcedure
            .CommandText = "st_available_lecturer"
        End With
        resultDataSet = ExecuteSQLCommandDS(SQLCommand)

        SQLCommand.Dispose()

        Return resultDataSet
    End Function

    Public Shared Function GetAvailableStudentDatabase() As DataSet
        Dim SQLCommand As New SqlCommand
        Dim resultDataSet As New DataSet
        With SQLCommand
            .CommandType = CommandType.StoredProcedure
            .CommandText = "st_available_student"
        End With
        resultDataSet = ExecuteSQLCommandDS(SQLCommand)

        SQLCommand.Dispose()

        Return resultDataSet
    End Function
    Public Shared Sub mRemoveLecturer(ByVal sLecturerNumber As String)
        Dim SQLCommand As New SqlCommand
        With SQLCommand
            .CommandType = CommandType.StoredProcedure
            .CommandText = "foRemoveLecturer"
        End With
        AddParameter(SQLCommand, "@LecturerNumber", SqlDbType.NVarChar, ParameterDirection.Input, sLecturerNumber)
        ExecuteSQLCommand(SQLCommand)
        SQLCommand.Dispose()
    End Sub
    Public Shared Sub mRemoveStudent(ByVal sStudentNumber As String)
        Dim SQLCommand As New SqlCommand
        With SQLCommand
            .CommandType = CommandType.StoredProcedure
            .CommandText = "foRemoveStudent"
        End With
        AddParameter(SQLCommand, "@StudentNumber", SqlDbType.NVarChar, ParameterDirection.Input, sStudentNumber)
        ExecuteSQLCommand(SQLCommand)
        SQLCommand.Dispose()
    End Sub

    Public Shared Sub mUpdateRoomSession(ByVal sValue As String, ByVal sGroup As String, ByVal sSession As String)
        Dim SQLCommand As New SqlCommand
        With SQLCommand
            .CommandType = CommandType.StoredProcedure
            .CommandText = "foUpdateRoomSession"
        End With
        AddParameter(SQLCommand, "@Value", SqlDbType.NVarChar, ParameterDirection.Input, sValue)
        AddParameter(SQLCommand, "@Group", SqlDbType.NVarChar, ParameterDirection.Input, sGroup)
        AddParameter(SQLCommand, "@Session", SqlDbType.NVarChar, ParameterDirection.Input, sSession)
        ExecuteSQLCommand(SQLCommand)
        SQLCommand.Dispose()
    End Sub

    Public Shared Sub mInsertLecturerSession(ByVal sValue As String, ByVal sGroup As String, ByVal sSession As String)
        Dim SQLCommand As New SqlCommand
        With SQLCommand
            .CommandType = CommandType.StoredProcedure
            .CommandText = "foInsertLecturerSession"
        End With
        AddParameter(SQLCommand, "@Value", SqlDbType.NVarChar, ParameterDirection.Input, sValue)
        AddParameter(SQLCommand, "@Group", SqlDbType.NVarChar, ParameterDirection.Input, sGroup)
        AddParameter(SQLCommand, "@Session", SqlDbType.NVarChar, ParameterDirection.Input, sSession)
        ExecuteSQLCommand(SQLCommand)
        SQLCommand.Dispose()
    End Sub

    Public Shared Sub mUpdateLecturerSession(ByVal sValue As String, ByVal sSessionID As String)
        Dim SQLCommand As New SqlCommand
        With SQLCommand
            .CommandType = CommandType.StoredProcedure
            .CommandText = "foUpdateLecturerSession"
        End With
        AddParameter(SQLCommand, "@Value", SqlDbType.NVarChar, ParameterDirection.Input, sValue)
        AddParameter(SQLCommand, "@Session", SqlDbType.NVarChar, ParameterDirection.Input, sSessionID)
        ExecuteSQLCommand(SQLCommand)
        SQLCommand.Dispose()
    End Sub
    Public Shared Sub mDeleteStudentSession(ByVal sValue As String)
        Dim SQLCommand As New SqlCommand
        With SQLCommand
            .CommandType = CommandType.StoredProcedure
            .CommandText = "foDeleteStudentSession"
        End With
        AddParameter(SQLCommand, "@Value", SqlDbType.NVarChar, ParameterDirection.Input, sValue)
        ExecuteSQLCommand(SQLCommand)
        SQLCommand.Dispose()
    End Sub
    Public Shared Sub mDeleteLecturerSession(ByVal sValue As String)
        Dim SQLCommand As New SqlCommand
        With SQLCommand
            .CommandType = CommandType.StoredProcedure
            .CommandText = "foDeleteLecturerSession"
        End With
        AddParameter(SQLCommand, "@Value", SqlDbType.NVarChar, ParameterDirection.Input, sValue)
        ExecuteSQLCommand(SQLCommand)
        SQLCommand.Dispose()
    End Sub
    Public Shared Sub mUpdateStudentSession(ByVal sGroup As String, ByVal sSession As String, ByVal sOrder As String, ByVal sValue As String)
        Dim SQLCommand As New SqlCommand
        With SQLCommand
            .CommandType = CommandType.StoredProcedure
            .CommandText = "foUpdateStudentSession"
        End With
        AddParameter(SQLCommand, "@Value", SqlDbType.NVarChar, ParameterDirection.Input, sValue)
        AddParameter(SQLCommand, "@Group", SqlDbType.NVarChar, ParameterDirection.Input, sGroup)
        AddParameter(SQLCommand, "@Session", SqlDbType.NVarChar, ParameterDirection.Input, sSession)
        AddParameter(SQLCommand, "@Order", SqlDbType.NVarChar, ParameterDirection.Input, sOrder)
        ExecuteSQLCommand(SQLCommand)
        SQLCommand.Dispose()
    End Sub





    Public Shared Function GetAvailableLecturer(ByVal sName As String, ByVal sSessionID As String) As String
        Dim dt As DataTable = GetAvailableLecturerDatabase.Tables(0)
        If dt.Rows.Count > 0 Then
            Dim sOut As StringBuilder = New StringBuilder("")
            sOut.Append("<select name='Lecturer_" & sSessionID & "' id='Lecturer_" & sSessionID & "'>")
            sOut.AppendFormat("<option value=''>{0}</option>", sName)
            For Each dr As DataRow In dt.Rows
                sOut.AppendFormat("<option value='{1}'>{0}/{1}/{2}</option>", dr("Ltr_Name"), dr("Ltr_Number"), dr("Ltr_Expertise"))
            Next
            sOut.Append("</select>")
            Return sOut.ToString
        Else
            Return sName
        End If
    End Function
    Public Shared Function GetAvailableStudent(ByVal sName As String) As String
        Dim dt As DataTable = GetAvailableStudentDatabase.Tables(0)
        If dt.Rows.Count > 0 Then
            Dim sOut As StringBuilder = New StringBuilder("")
            sOut.Append("<select name='Student_ChangeTime' id='Student_ChangeTime'>")
            sOut.AppendFormat("<option value=''>{0}</option>", sName)
            For Each dr As DataRow In dt.Rows
                sOut.AppendFormat("<option value='{0}'>{1}/{0}/{2}</option>", dr("Std_Number"), dr("Std_Name"), dr("Std_WPPGrp"))
            Next
            sOut.Append("</select>")
            Return sOut.ToString
        Else
            Return sName
        End If
    End Function
#End Region



#Region "tuan part"

    Public Shared Function ds_StudentSession(ByVal sTable As String) As DataSet
        Dim SQLCommand As New SqlCommand
        Dim resultDataSet As New DataSet
        With SQLCommand
            .CommandType = CommandType.StoredProcedure
            .CommandText = "tnStudentSession"
        End With
        resultDataSet = ExecuteSQLCommandDS(SQLCommand)
        resultDataSet.Tables(0).TableName = sTable
        SQLCommand.Dispose()

        Return resultDataSet
    End Function

#End Region

End Class
